package com.buluo.service.sql

import com.buluo.model.TestModel
import com.m1905hd.common.util.Page
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.jdbc.core.support.JdbcDaoSupport
import org.springframework.stereotype.Service
import org.springframework.util.StringUtils

import javax.annotation.Resource

/**
 * Created by sunhao on 2017/1/11.
 */
@Service
class TestSqlService extends JdbcDaoSupport {

    @Resource
    public void setJb(JdbcTemplate jb) {
        super.setJdbcTemplate(jb);
    }

    def findTest(int id){
        def query = "from test_model"
        if (id > 0) {
            query += " where id=" + id
        }
        def data = jdbcTemplate.queryForList("select * " + query + " limit 1")
        return data
    }

    Page listTest(int id, String title, String info, int page, int size) {
        def query = "from test_model"
        if (id > 0) {
            query += " where id=" + id
        }
        if (!StringUtils.isEmpty(title)) {
            if (query.contains("where")) {
                query += " and title='${title}'"
            } else {
                query += " where title='${title}'"
            }
        }
        if (!StringUtils.isEmpty(info)) {
            if (query.contains("where")) {
                query += " and info='${info}'"
            } else {
                query += " where info='${info}'"
            }
        }
        int total = jdbcTemplate.queryForInt("select count(0) " + query)
        Page result = new Page(total, page, size);
        def pageArg = pageInit(page,size)
        def list = jdbcTemplate.queryForList("select * " + query + " order by id desc limit ${pageArg.start},${pageArg.offset}")
        result.setData(list)
        return result
    }

    private pageInit(int page, int size) {
        if (size < 0 || size >= 20) {
            size = 20;
        }
        if (page <= 0) {
            page = 1;
        }
        def start = (page - 1) * size
        def offset = size
        return [start: start, offset: offset]
    }

}
